<?php
class facture_mdl extends CI_Model
{
	
	
	public function get_facture($chemin)
	{
			 $four = $this->input->post('fournisseur');
		     $mont = $this->input->post('montant');

			 $enga =$this->input->post('engagement');

	$query =	$this->db->query("
INSERT INTO `facture` (`id_fournisseur_fk`, `montant_fact`, `id_engagement_fk`, `id_mandat_fk` , `image`) VALUES ('$four', '$mont', '$enga', '2', '$chemin')");
    //return $query->result();
	}
	
	 public function get_all($limit ,$start) {
	  	
      $q = $this->db->query("SELECT
facture.id_facture,
facture.id_fournisseur_fk,
fournisseurs.nom,
facture.montant_fact,
facture.id_engagement_fk,
facture.id_mandat_fk,
facture.image,
facture.numero,
mandats.numero AS numero_mondat
FROM
facture
INNER JOIN fournisseurs ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN mandats ON facture.id_mandat_fk = mandats.id_mandat
LIMIT  $start ,$limit
");

return $q->result();

    }
	
	public function get_all_nom() {
	  	
      $q = $this->db->query("SELECT
facture.id_facture,
facture.id_fournisseur_fk,
fournisseurs.nom,
facture.montant_fact,
facture.id_engagement_fk,
facture.id_mandat_fk
FROM
facture
INNER JOIN fournisseurs ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
");

return $q->result();

    }
	
	
	public function get_all_per_article($art) {
	  	
      $q = $this->db->query("SELECT
fournisseurs.nom,
facture.id_facture,
fourn_article.id_article_fk,
facture.montant_fact,
facture.id_mandat_fk,
facture.image
FROM
	fournisseurs
INNER JOIN facture ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN fourn_article ON fourn_article.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
	fourn_article.id_article_fk = $art
");

return $q->result();

    }
	
	public function get_details($id) {
	  	
      $q = $this->db->query("SELECT
fournisseurs.nom AS fournisseur,
facture.montant_fact,
facture.id_facture,
facture.id_mandat_fk,
facture.id_fournisseur_fk,
facture.image,
facture.numero,
mandats.numero AS num_mondat,
mandats.montant,
mandats.id_mandat,
bcs.id_bc,
conventions.id_convetion
FROM
facture
INNER JOIN fournisseurs ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN mandats ON facture.id_mandat_fk = mandats.id_mandat
LEFT JOIN bcs ON bcs.id_facture_fk = facture.id_facture
LEFT JOIN conventions ON conventions.id_facture_fk = facture.id_facture
WHERE
facture.id_facture = $id

");

return $q->result();

    }
	
	public function supprime_fact($id) {
	  	
      $q = $this->db->query("DELETE FROM `facture` WHERE (`id_facture`='$id')
");

    }

    public function annule_facture_bc($id_bc) {
	  	
      $q = $this->db->query("UPDATE `bcs` SET `id_facture_fk`=NULL WHERE (`id_bc`='$id_bc')
");

    }
	
	 public function annule_facture_conv($id_conv) {
	  	
      $q = $this->db->query("UPDATE `conventions` SET `id_facture_fk`=NULL WHERE (`id_convetion`='$id_conv')
");

    }
	
	
		public function get_bc_conv_liee($id) {
	  	
      $q = $this->db->query("SELECT
facture.id_facture,
facture.numero,
bcs.id_bc,
conventions.id_convetion
FROM
facture
LEFT JOIN bcs ON bcs.id_facture_fk = facture.id_facture
LEFT JOIN conventions ON conventions.id_facture_fk = facture.id_facture
WHERE
facture.id_facture = $id

");
return $q->result();
    }
	
	public function get_recherche($limit ,$start,$nom) {
	  	
      $q = $this->db->query("SELECT
facture.id_facture,
facture.montant_fact,
facture.id_mandat_fk,
fournisseurs.nom
FROM
facture
INNER JOIN fournisseurs ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN fourn_article ON fourn_article.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
fournisseurs.nom LIKE '%$nom%'
LIMIT  $start ,$limit
");

return $q->result();

    }
	
	public function get_per_article($limit ,$start,$art) {
	  	
      $q = $this->db->query("SELECT
fournisseurs.nom,
facture.id_facture,
facture.montant_fact,
facture.id_mandat_fk,
facture.image,
bcs.id_article_fk AS bc_art,
conventions.id_article_fk AS conv_art,
mandats.numero AS numero_mondat,
facture.numero,
nom_art.nom AS nom_art
FROM
fournisseurs
INNER JOIN facture ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN bcs ON bcs.id_facture_fk = facture.id_facture
LEFT JOIN conventions ON conventions.id_facture_fk = facture.id_facture
LEFT JOIN mandats ON facture.id_mandat_fk = mandats.id_mandat
LEFT JOIN articles AS nom_art ON bcs.id_article_fk = nom_art.id_article OR conventions.id_article_fk = nom_art.id_article
WHERE
bcs.id_article_fk = $art OR
conventions.id_article_fk = $art
	LIMIT  $start ,$limit
");

return $q->result();

    }
	 public function record_count_all() {
	  	
      $q = $this->db->query("SELECT
facture.id_facture,
facture.id_fournisseur_fk,
fournisseurs.nom,
facture.montant_fact,
facture.id_engagement_fk,
facture.id_mandat_fk
FROM
facture
INNER JOIN fournisseurs ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur

");

$cont=$q->num_rows();
return $cont;

    }
	
	 public function record_count_art($art) {
	  	
      $q = $this->db->query("SELECT
	fournisseurs.nom,
	facture.id_facture,
	fourn_article.id_article_fk
FROM
	fournisseurs
INNER JOIN facture ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
INNER JOIN fourn_article ON fourn_article.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
	fourn_article.id_article_fk = $art
");

$cont=$q->num_rows();
return $cont;

    }

    public function get_update($id_fact,$nom_fournisseur,$nom_art,$montant,$mondat,$num_engagement,$table,$image)
	{
		$query1 =	$this->db->query("
SELECT
engagements.id_engagement
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE
articles.nom = '$nom_art' AND
engagements.numero = $num_engagement
 ");
    $cont=$query1->num_rows();
	$r=$query1->result();
	$id_enga=$r[0]->id_engagement;
	
	
			
	$query =	$this->db->query("
UPDATE `facture` SET `montant_fact` = '$montant' , `id_mandat_fk`= '$mondat' , `id_engagement_fk`='$id_enga' , `image`= '$image' WHERE (`id_facture`='$id_fact')  ");
    //return $query->result();
	}

    public function ajout_new($nom_fournisseur,$nom_art,$montant,$numero,$image)
	{
		/*$query1 =	$this->db->query("
SELECT
engagements.id_engagement
FROM
engagements
INNER JOIN articles ON engagements.id_article_fk = articles.id_article
WHERE
articles.nom = '$nom_art' AND
engagements.numero = $num_engagement
 ");
    $cont=$query1->num_rows();
	$r1=$query1->result();
	$id_enga=$r1[0]->id_engagement;*/
	
	$query2 =	$this->db->query("
SELECT
fournisseurs.id_fournisseur,
fournisseurs.nom
FROM
fournisseurs
WHERE
fournisseurs.nom = '$nom_fournisseur'

 ");
    $cont=$query2->num_rows();
	$r2=$query2->result();
	$id_fourn=$r2[0]->id_fournisseur;
			
	$query =	$this->db->query("
INSERT INTO `facture` (`id_fournisseur_fk`, `montant_fact`, `numero`, `image`) VALUES ('$id_fourn', '$montant', '$numero', '$image') ");
    //return $query->result();
	}

     public function get_new_id() {
	  	
      $q = $this->db->query("SELECT
facture.id_facture
FROM
facture
ORDER BY
facture.id_facture DESC
LIMIT 0, 1");


return $q->result();

    }

     public function attacher_facture_mondat($numero,$id_fournisseur,$id_mondat) {
	 	
	  
	  $q = $this->db->query("UPDATE `facture` SET `id_mandat_fk`='$id_mondat' WHERE (`numero`='$numero' AND id_fournisseur_fk='$id_fournisseur')");
	
}
     


	 public function verifie_facture_numero($numero,$id_fournisseur) {
	 	
	   $query = $this->db->query("SELECT facture.id_facture
FROM facture
WHERE
facture.numero = $numero AND
facture.id_fournisseur_fk =$id_fournisseur
"); 	
$cont=$query->num_rows();
if($cont>0){
	 // $q = $this->db->query("UPDATE `facture` SET `id_mandat_fk`='$id_mondat' WHERE (`numero`='$numero' AND id_fournisseur_fk='$id_fournisseur')");
	  return TRUE;
}else{
	return FALSE;
}
     


//return $q->result();

    }
  
   public function verifie_id($id) {
	  	
      $q = $this->db->query("SELECT
facture.id_facture,
facture.montant_fact
FROM
facture
WHERE
facture.id_facture = $id");


return $q->result();

    }

     public function attacher_bc_facture($id_bc,$id_fact) {
	  	
      $q = $this->db->query("UPDATE `bcs` SET `id_facture_fk`='$id_fact' WHERE (`id_bc`='$id_bc')");

    }

      public function attacher_conv_facture($id_conv,$id_fact) {
	  	
      $q = $this->db->query("UPDATE `conventions` SET `id_facture_fk`='$id_fact' WHERE (`id_convetion`='$id_conv')");




    }

      public function attacher_mondat_facture($id_mondat,$id_fact) {
	  	
      $q = $this->db->query("UPDATE `facture` SET `id_mandat_fk`='$id_mondat' WHERE (`id_facture`='$id_fact')");




    }

       public function get_numero_bc($id_fourn) {
	  	
      $q = $this->db->query("SELECT
bcs.id_bc,
bcs.date,
bcs.numero,
fournisseurs.nom
FROM
bcs
INNER JOIN fournisseurs ON bcs.id_fournisseur_fk = fournisseurs.id_fournisseur
WHERE
fournisseurs.id_fournisseur = '$id_fourn' AND
bcs.id_facture_fk IS NULL AND
bcs.id_engagement_fk IS NOT NULL
");

return $q->result();


    }
        
		 public function get_numero_conv($id_fourn)
	{


	$query = $this->db->query("SELECT
conventions.id_convetion,
conventions.numero
FROM
conventions
WHERE
conventions.id_facture_fk IS NULL AND
conventions.id_fourn_fk = $id_fourn
 ");
	
return $query->result();

	}
	
        public function existe_facture($numero,$id_fourn) {
	  	
      $q = $this->db->query("SELECT
facture.id_facture
FROM
facture
WHERE
facture.numero = $numero AND
facture.id_fournisseur_fk = $id_fourn

");

return $q->result();


    }

        public function facture_non_payer($limit,$start) {
	  	
      $q = $this->db->query("SELECT
facture.id_facture,
facture.montant_fact,
facture.id_mandat_fk,
facture.image,
mandats.numero AS numero_mondat,
facture.numero,
fournisseurs.nom
FROM
facture
INNER JOIN fournisseurs ON facture.id_fournisseur_fk = fournisseurs.id_fournisseur
LEFT JOIN mandats ON facture.id_mandat_fk = mandats.id_mandat
WHERE
facture.id_mandat_fk IS NULL
LIMIT  $start ,$limit
");

return $q->result();


    }

      public function ajout_new_facture($id_fourn,$montant,$numero) {
	  	
      $q = $this->db->query("INSERT INTO `facture` (`id_fournisseur_fk`, `montant_fact`, `numero`) VALUES ('$id_fourn', '$montant', '$numero')
");

    }

       public function ajout_new_facture_direct($id_fourn,$montant,$numero,$id_art) {
	  	
      $q = $this->db->query("INSERT INTO `facture` (`id_fournisseur_fk`, `montant_fact`, `numero`, `id_engagement_fk`) VALUES ('$id_fourn', '$montant', '$numero','$id_art')
");

    }
	
	 public function ajout_image($id_fact,$image) {
	  	
      $q = $this->db->query("UPDATE `facture` SET `image`='$image' WHERE (`id_facture`='$id_fact')
");

    }
}

?>